package com.sino.second.dao.hibernate;

import java.sql.SQLException;
import java.util.Collection;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.sino.bean.Basicinfo;
import com.sino.bean.TblZhuanjiarukuxinxibiao;
import com.sino.second.bean.TblSystemparam;
import com.sino.second.bean.TblXmjg1;
import com.sino.second.dao.IExpertsReviewDAO;
import com.sino.util.SysParameter;
import com.sino.util.page.AbstractPagerManager;
import com.sino.util.page.PagerModel;

public class Review1DAO extends AbstractPagerManager implements
		IExpertsReviewDAO {
	// 根据专家ID查找所评审的项目
	public List findReviewXmListByZjId(Integer zjid, String hqlWhere) {
		String str_SQL = "select tx1.basicinfo,sum(tx1.xmfs) as xmfs,sum(tx1.fzqz) as fzqz from TblXmfs1 tx1 where tx1.tblZhuanjiarukuxinxibiao.FBh=? ";
		if (hqlWhere.length() > 1) {
			str_SQL += " and " + hqlWhere;
		}
		str_SQL += "  group by tx1.basicinfo.zlid order by sum(tx1.fzqz) desc";
		return this.getHibernateTemplate().find(str_SQL, new Object[] { zjid });
	}

	// 根据专利ID查找所评审的项目
	public List findReviewXmListByZlId(String zlid, String hqlWhere) {
		String str_SQL = "select tx1.tblZhuanjiarukuxinxibiao, tx1.basicinfo.zlid,sum(tx1.xmfs) as xmfs,sum(tx1.fzqz) from TblXmfs1 tx1 where tx1.basicinfo.zlid=? ";
		if (hqlWhere.length() > 1) {
			str_SQL += " and " + hqlWhere;
		}
		str_SQL += "  group by tx1.tblZhuanjiarukuxinxibiao.FBh order by sum(tx1.fzqz)";
		return this.getHibernateTemplate().find(str_SQL, new Object[] { zlid });
	}

	public List findReviewsList(String zlid, Integer zjid) {
		String str_SQL = "from TblXmfs1 tx1 where tx1.basicinfo.zlid=? and tx1.tblZhuanjiarukuxinxibiao.FBh=? order by tx1.sysXmpszb.id";
		return this.getHibernateTemplate().find(str_SQL,
				new Object[] { zlid, zjid });
	}

	public void saveOrUpdateReviews(Collection o) {
		this.getHibernateTemplate().saveOrUpdateAll(o);
	}

	// xyw
	public void saveOrUpdateReviews1(Object o) {
		this.getHibernateTemplate().saveOrUpdate(0);
	}

	public List findReviewXmList(final int firstRow, final int maxRow,
			final String xkId, String hqlWhere) {
		String str_SQL = "from Basicinfo basicinfo where isqualified=1 and basicinfo.xk.id=?";
		if (hqlWhere.length() > 1) {
			str_SQL += " and " + hqlWhere;
		}
		final String queryhql = str_SQL;
		return this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(queryhql);
				query.setFirstResult(firstRow);
				query.setMaxResults(maxRow);
				query.setString(0, xkId);
				List<Basicinfo> list = (List<Basicinfo>) query.list();
				return list;
			}
		});
	}

	/**
	 * 项目列表记录数
	 * 
	 * @param xk
	 *            学科
	 * @param xkId
	 *            专利ID
	 * @return
	 */
	public int findReviewXmCount(String xkId, String hqlWhere) {
		String str_SQL = "select count(*) from Basicinfo basicinfo where isqualified=1 and basicinfo.xk.id=?";
		if (hqlWhere.length() > 1) {
			str_SQL += " and " + hqlWhere;
		}
		return (Integer) this.getHibernateTemplate().find(str_SQL,
				new Object[] { xkId }).get(0);
	}

	public int findReviewCount(String hqlWhere) {
		String str_SQL = "select count(*)  from  TblXmfs1 tx1 where '1'='1'";
		if (hqlWhere.length() > 1) {
			str_SQL += " and " + hqlWhere;
		}

		return (Integer) this.getHibernateTemplate().find(str_SQL).get(0);
	}

	public Object findReviewResultsByZlid(String zjid) {
		// TODO Auto-generated method stub
		return null;
	}

	public List findReviewResultsList(String hqlWhere) {
		String str_SQL = "from TblXmjg1 txj1 where '1'='1' and year="
				+ SysParameter.CURRENT_YEAR;
		if (hqlWhere.length() > 1) {
			str_SQL += " and " + hqlWhere;
		}
		str_SQL += " order by txj1.basicinfo.xk,txj1.xmpjpxfx desc";
		return this.getHibernateTemplate().find(str_SQL);
	}

	public void saveReviewsResults(Object o) {
		this.getHibernateTemplate().saveOrUpdate(o);

	}

	public List<?> findReviewXmFsList(String hqlWhere) {
		// TODO Auto-generated method stub
		return null;
	}

	@SuppressWarnings("unchecked")
	public List<TblXmjg1> findReviewXmJgList(String hqlWhere) {
		String hql = "from TblXmjg1 xmjg";
		if ("" != hqlWhere && hqlWhere.length() > 0) {
			hql = hql + " where " + hqlWhere;
		}
		return (List<TblXmjg1>) this.getHibernateTemplate().find(hql);
	}

	public void updateReviewsResults(Object o) {
		// TODO Auto-generated method stub

	}

	public void updateReviewsState(String basicInfoId, String state) {
		// TODO Auto-generated method stub

	}

	public List<TblZhuanjiarukuxinxibiao> listReviewExpertByBasicInfoId(
			String basicInfoId) {
		// TODO Auto-generated method stub
		return null;
	}

	/**
	 * xyw 根据项目id查找该项目被多个专家打的总分
	 */

	public Object findSumFenshuByZlid(String zlid) {
		// TODO Auto-generated method stub
		String str_SQL = "select sum(tx1.fzqz) from TblXmfs1 as tx1 group by tx1.basicinfo.zlid having tx1.basicinfo.zlid='"
				+ zlid + "'" + "order by sum(tx1.fzqz) desc";
		List list = this.getHibernateTemplate().find(str_SQL);
		if (list.size() == 0) {
			return null;
		} else {
			return list.get(0);
		}

	}

	/**
	 * xyw 根据项目id查出有多少个专家为该项目打过分
	 */
	public int findCountZhuanjiaByZlid(String zlid) {
		// TODO Auto-generated method stub
		String str_SQl = "select count(distinct tx1.tblZhuanjiarukuxinxibiao) from TblXmfs1 tx1 where tx1.basicinfo.zlid='"
				+ zlid + "'";
		return (Integer) this.getHibernateTemplate().find(str_SQl).get(0);
	}

	/**
	 * xyw 根据zlid查找专家
	 * 
	 * @param hqlWhere
	 * @return
	 */
	public List findZhuanjiaByZlid(String zlid) {
		// TODO Auto-generated method stub
		String str_SQl = "select distinct tx1.tblZhuanjiarukuxinxibiao from TblXmfs1 tx1 where tx1.basicinfo.zlid='"
				+ zlid + "'";
		return this.getHibernateTemplate().find(str_SQl);
	}

	/**
	 * xyw 保存结果
	 * 
	 * @param xmjg1
	 */
	public void saveTblXmjg1(TblXmjg1 xmjg1) {
		this.getHibernateTemplate().save(xmjg1);
	}

	/**
	 * xyw 根据zlid查项目的状态 0评审中1评审完毕
	 */
	public Object findStateByZlid(String zlid) {
		// TODO Auto-generated method stub
		String str_SQl = "select tx1.ispass from TblXmjg1 tx1 where tx1.basicinfo.zlid='"
				+ zlid + "'";
		if (0 == this.getHibernateTemplate().find(str_SQl).size()) {
			return null;
		} else {
			return this.getHibernateTemplate().find(str_SQl).get(0);
		}
	}

	/**
	 * xyw查询复审分数表是否有值 然后判断是否还可以进行初审
	 */

	public int findCountReviewXmFsList(String hqlWhere) {
		// TODO Auto-generated method stub
		String hql = "select count(distinct xmfs2.basicinfo)"
				+ "from TblXmfs2 xmfs2 ";
		if ("" != hqlWhere && hqlWhere.length() > 0) {
			hql = hql + "where " + hqlWhere;
		}
		return (Integer) this.getHibernateTemplate().find(hql).get(0);
	}

	public PagerModel findReviewResultsPageList(String hqlWhere) {
		// TODO Auto-generated method stub
		return null;
	}

	/**
	 * wyw 根据zlid查找项目信息
	 */
	public Object findXmByZlid(String zlid) {
		// TODO Auto-generated method stub

		String str_SQl = "select * from Basicinfo tx1 where tx1.basicinfo.zlid='"
				+ zlid + "'";
		if (0 == this.getHibernateTemplate().find(str_SQl).size()) {
			return null;
		} else {
			return this.getHibernateTemplate().find(str_SQl).get(0);
		}
	}

	/**
	 * xyw 根据xkid 和专业组id查找专家个数
	 * 
	 * @param xmpsmcId
	 * @param zhuanyeId
	 * @param hqlWhere
	 * @return
	 */
	public Integer findZjpsfpCountByXmpsmcId(String xmpsmcId, String zhuanyeId,
			String hqlWhere) {
		String hql = "select count(*) from TblZjpsfp tzpf where tzpf.sysXmpsmc.id=?";
		if (!"".equals(zhuanyeId) && zhuanyeId.length() > 0) {
			hql = hql + " and tzpf.tblZhuanjiarukuxinxibiao.sysZhuanyema.bm=?";
			if (!"".equals(hqlWhere) && hqlWhere.length() > 0) {
				hql = hql + " and " + hqlWhere;
			}
			return (Integer) this.getSession().createQuery(hql).setParameter(0,
					xmpsmcId).setParameter(1, zhuanyeId).uniqueResult();
		}
		if (!"".equals(hqlWhere) && hqlWhere.length() > 0) {
			hql = hql + " and " + hqlWhere;
		}
		return (Integer) this.getSession().createQuery(hql).setParameter(0,
				xmpsmcId).uniqueResult();
	}

	/**
	 * xyw 根据xkid找基本信息表 项目的个数
	 */
	public Integer findXmCountByXkId(String xkId) {
		String hql1 = "select count(b.zlid) from Basicinfo b where b.xk.id='"
				+ xkId + "'";
		List l = this.getHibernateTemplate().find(hql1);
		String o1 = l.get(0).toString();
		int count = Integer.parseInt(o1);
		if (null == o1) {
			return null;
		} else {
			return count;
		}
	}

	/**
	 * xyw 根据xkid找分数表1项目不重复的个数
	 */
	public Integer findFsCountByXkId(String xkId) {
		String hql2 = "select count(distinct t.basicinfo.zlid) from TblXmfs1 t where t.basicinfo.zlid in (select b.zlid from Basicinfo b where b.xk.id='"
				+ xkId + "')";
		List l2 = this.getHibernateTemplate().find(hql2);
		String o2 = l2.get(0).toString();
		int count2 = Integer.parseInt(o2);
		if (null == o2) {
			return null;
		} else {
			return count2;
		}
	}
	/**
	 * xyw 设置申报时间
	 * 
	 */
	public void saveOrUpdateTime(TblSystemparam t, TblSystemparam t2) {
		// TODO Auto-generated method stub
		String hql01 = "from TblSystemparam t where t.paramName='beginTime'";
		String hql02 = "from TblSystemparam t where t.paramName='endTime'";
		TblSystemparam beginTime = (TblSystemparam) this.getSession()
				.createQuery(hql01).uniqueResult();

		TblSystemparam endTime = (TblSystemparam) this.getSession()
				.createQuery(hql02).uniqueResult();
		if (null != beginTime) {
			beginTime.setParamValue(t.getParamValue());
		} else {
			this.getHibernateTemplate().save(t);
		}
		if (null != endTime) {
			endTime.setParamValue(t2.getParamValue());
		} else {
			this.getHibernateTemplate().save(t2);
		}
		this.getSession().flush();

	}

	public List listReviewFsList(String zlid, String hqlWhere) {
		// TODO Auto-generated method stub
		return null;
	}

	public List<Integer> listReviewFsExpertIdList(String hqlWhere) {
		// TODO Auto-generated method stub
		return null;
	}

	public List listReviewXmIdList(int expertId, String hqlWhere) {
		// TODO Auto-generated method stub
		return null;
	}
}